package lpl.com.xyd.test.manage.common.utils.excel;

import com.alibaba.fastjson.JSONObject;
import lpl.com.xyd.test.manage.common.utils.DateFormatEnum;
import lpl.com.xyd.test.manage.common.utils.ExcelTitleEnum;
import lpl.com.xyd.test.manage.common.utils.entity.ExcelEntityDTO;
import lpl.com.xyd.test.manage.common.utils.entity.ExcelSheetDTO;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * excel 的导入导出
 * @Author mingliang
 * @Date 2017-12-27 10:20
 */
public class ExcelUtils {

    private static String timeFormat = "yyyy-MM-dd HH:mm:ss";
    private static SimpleDateFormat sf = new SimpleDateFormat(timeFormat);

    /**
     * 读取Excel表格
     * @param inputStream 输入流
     * @return 返回一个json格式数据
     * @throws IOException
     */
    public static Map<String, List<JSONObject>> loadExcel(InputStream inputStream) throws Exception {
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook workbook = new XSSFWorkbook(inputStream);
        //获取Excel文档中的第一个表单
        Sheet sht0 = workbook.getSheetAt(0);
        Sheet sht1 = workbook.getSheetAt(1);
        //对Sheet中的每一行进行迭代
//        JSONObject result = new JSONObject();

        List<String> titles1 = new ArrayList<>();
        titles1.add("周报NO");
        titles1.add("周报");
        List<String> titles2 = ExcelTitleEnum.getTitle();
        List<JSONObject> list1 = new ArrayList<>();
        List<JSONObject> list2 = new ArrayList<>();
        readSheet(list1,sht0,titles1);
        readSheet(list2,sht1,titles2);
        Map<String, List<JSONObject>> resultMap = new HashMap<>();
        resultMap.put("key1",list1);
        resultMap.put("key2",list2);
//        result.put("data", resultMap);
        inputStream.close();
        return resultMap;
    }

    private static void readSheet(List<JSONObject> list,Sheet sht,List<String> titles){
        sht.forEach(row->{
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环,第一行为表头
            if (row.getRowNum()>1){
                int count = row.getLastCellNum();
                JSONObject object = new JSONObject();
                for (int i = 0; i < count; i++) {
                    String code = ExcelTitleEnum.getByMessage(titles.get(i)).getCode();
                    Cell cell = row.getCell(i);
                    if (cell==null || cell.toString().trim().equals("")) {
                        continue;
                    }
                    if (null == code || code.isEmpty()){
                        throw new RuntimeException("Excel 表头匹配错误！titleName = {"+titles.get(i)+"} ");
                    }
                    try {
                        object.put(code, getCellValue(row.getCell(i),code));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (object.entrySet().size() > 0){
                    list.add(object);
                }
            }
        });
    }

    /**
     * 写入Excel
     * @param excelEntityDTO
     * @return XSSFWorkbook
     */
    public static XSSFWorkbook writeExcel(ExcelEntityDTO excelEntityDTO) throws Exception {

        List<ExcelSheetDTO> excelSheetDTOS = excelEntityDTO.getExcelSheetDTOS();

        // 创建一个Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

//        XSSFCellStyle cellStyle = setStyle(workbook);
        XSSFCellStyle cellStyle = workbook.createCellStyle();
//        XSSFDataFormat format = workbook.createDataFormat();
//        cellStyle.setDataFormat(format.getFormat("@"));
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底部
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直顶部
        cellStyle.setWrapText(true);//设置自动换行
        XSSFFont font = workbook.createFont();
//        font.setFontName("黑体");
//        font.setFontHeightInPoints((short) 16);//设置字体大小
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

        cellStyle.setFont(font);//选择创建的字体格式

        XSSFCellStyle rowStyle = setRowStyle(workbook);
        // 创建sheet
        for (ExcelSheetDTO excelSheet : excelSheetDTOS){
            XSSFSheet sheet = workbook.createSheet(excelSheet.getSheetName());
            sheet.autoSizeColumn(0);
            sheet.setColumnWidth(0, 3766);
            XSSFRow row = sheet.createRow(0);
            row.setHeight((short) 500);
            // 表头
            List<String> title = excelSheet.getTitleList();
            List<Integer> width = excelSheet.getWidth();
            for (int i = 0; i < title.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(ExcelTitleEnum.getMsgByCode(title.get(i)));
                cell.setCellStyle(cellStyle);
                cell.setCellStyle(cellStyle);//设置单元格格式为"文本"
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);

                sheet.setColumnWidth(i, width.get(i));
//                System.out.println(width.get(i));
            }

            // 设置值
            List<Map<String,Object>> valueList = excelSheet.getValueList();
            setValue(valueList,sheet,rowStyle,workbook.createCellStyle());
        }
        return workbook;
    }

    /**
     *
     * @param workbook
     * @return
     */
    private static XSSFCellStyle setStyle(XSSFWorkbook workbook){
        // 设置cell样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 加粗
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 背景色的设定
        cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式
        cellStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        return cellStyle;
    }

    /**
     *
     * @param workbook
     * @return
     */
    private static XSSFCellStyle setRowStyle(XSSFWorkbook workbook){
        // 每行样式
        XSSFCellStyle rowStyle = workbook.createCellStyle();
        rowStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        return rowStyle;
    }

    private static void setValue(List<Map<String,Object>> valueList,XSSFSheet sheet,
                                 XSSFCellStyle rowStyle,XSSFCellStyle cellStyle) throws Exception {

//        XSSFDataFormat format = workbook.createDataFormat();
//        cellStyle.setDataFormat(format.getFormat("@"));
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底部
        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直顶部
        cellStyle.setWrapText(true);//设置自动换行

        int i = 0;
        for (Map<String,Object> valueMap : valueList){
            XSSFRow row = sheet.createRow(i+1);
            row.setRowStyle(rowStyle);
            int j = 0;
            for (Map.Entry entry : valueMap.entrySet()){
                String type = ExcelTitleEnum.getByCode((String) entry.getKey()).getType();
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                switch (type){
                    case "int":
                        cell.setCellValue((int) entry.getValue());
                        break;
                    case "double":
                        cell.setCellValue((double) entry.getValue());//
                        break;
                    case "String":
                        cell.setCellValue((String) entry.getValue());
                        break;
                    case "Date":
                        try {
                            cell.setCellValue(sf.format((Date) entry.getValue()));
                        }catch (Exception e){
                            throw new RuntimeException("时间格式转换错误，入参类型不是Date类型 ！");
                        }
                        break;
                    case "Boolean":
                        cell.setCellValue((boolean) entry.getValue());
                        break;
                    case "Long":
                        cell.setCellValue(String.valueOf(entry.getValue()));
                        break;
                    case "long":
                        cell.setCellValue(String.valueOf(entry.getValue()));
                        break;
                    default:
                        // 其他类型按照String处理
                        cell.setCellValue((String) entry.getValue());
                        break;
                }
                j++;
            }
            i++;
        }
    }

    private static Object getCellValue(Cell cell,String code) throws Exception {
        String type = ExcelTitleEnum.getByCode(code).getType();
        try{
            switch (type){
                case "int":
                    return Integer.valueOf(String.valueOf(getValue(cell)));
                case "double":
                    return Double.parseDouble(String.valueOf(getValue(cell)));
                case "String":
                    return String.valueOf(getValue(cell)).replace(".0","");
                case "Date":
                    return sf.format(getValue(cell));
                case "Boolean":
                    return Boolean.valueOf(String.valueOf(getValue(cell)))?"Y":"N";
                case "Long":
                    return String.valueOf(getValue(cell));
                case "long":
                    return String.valueOf(getValue(cell));
                default:
                    return "";
            }
        }catch (NumberFormatException e){
            throw new RuntimeException("数字格式错误！");
        }
    }

    /**
     * Excel 类型判断
     * @param cell
     * @return
     */
    private static Object getValue(Cell cell){
        if (null != cell) {
            switch (cell.getCellType()) {
                // 数字
                case XSSFCell.CELL_TYPE_NUMERIC:
                    //判断单元格的类型是否则NUMERIC类型
                    if (0 == cell.getCellType()) {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            return cell.getDateCellValue();
                        } else {
                            return cell.getNumericCellValue();
                        }
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING: // 字符串
                    // 判断是否为时间字符串
                    String value = cell.getStringCellValue();
                    if (strIsDate(value)){
                        try {
                            String dateStr = DateFormatEnum.getTimeByDateStr(value);
                            if (null != dateStr && !dateStr.isEmpty()){
                                return sf.parse(dateStr);
                            }else {
                                return null;
                            }
                        } catch (ParseException e) {
                            return null;
                        }
                    }else {
                        return value;
                    }
                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    return cell.getBooleanCellValue();
                case XSSFCell.CELL_TYPE_FORMULA: // 公式
                    NumberFormat numberFormat = NumberFormat.getInstance();
                    // 设置精确到小数点后2位
                    numberFormat.setMaximumFractionDigits(2);
                    String defectRate = numberFormat.format(cell.getNumericCellValue()*100);
                    return defectRate+ "%";
                case XSSFCell.CELL_TYPE_BLANK: // 空值
                    return "";
                case XSSFCell.CELL_TYPE_ERROR: // 故障
                    new NumberFormatException("非法类型！");
                default:
                    return "";
            }
        }
        return null;
    }

    private static boolean strIsDate(String str){
        Pattern pattern = Pattern.compile(DateFormatEnum.getRegString());
        Matcher m = pattern.matcher(str);
        if (m.matches()) {
            return true;
        } else {
            return false;
        }
    }

//    public static void main(String[] args) {
//        try {
//            Map<String, List<JSONObject>> map = readfile("C:\\Users\\andy\\Desktop\\Excel");
//            testWrit("D:\\code",map);
////            FileInputStream fileIn = new FileInputStream("C:\\Users\\Administrator\\Desktop\\债权转让表字段内容.xlsx");
////            loadExcel(fileIn);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    /**
     * 读取某个文件夹下的所有文件
     * @param filepath
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static Map<String, List<JSONObject>> readfile(String filepath) throws FileNotFoundException, IOException {
        try {

            File file = new File(filepath);
            Map<String, List<JSONObject>> resultMap = new HashMap<>();
            List<JSONObject> list1 = new ArrayList<>();
            List<JSONObject> list2 = new ArrayList<>();
            if (!file.isDirectory()) {
                System.out.println("文件");
                Map<String, List<JSONObject>> object = loadExcel(new FileInputStream(file));
                object.forEach((k,v)->{
                    if (k.equals("key1")){
                        list1.addAll(v);
                    }else {
                        list2.addAll(v);
                    }
                });
            } else if (file.isDirectory()) {
                System.out.println("文件夹");
                String[] filelist = file.list();
                for (int i = 0; i < filelist.length; i++) {
                    File readfile = new File(filepath + "\\" + filelist[i]);
                    if (!readfile.isDirectory()) {
                        System.out.println("文件");
                        Map<String, List<JSONObject>> object = loadExcel(new FileInputStream(readfile));
                        object.forEach((k,v)->{
                            if (k.equals("key1")){
                                list1.addAll(v);
                            }else {
                                list2.addAll(v);
                            }
                        });
                        System.out.println("path=" + readfile.getPath()+"  name=" + readfile.getName());
                    } else if (readfile.isDirectory()) {
                        readfile(filepath + "\\" + filelist[i]);
                    }
                }
            }
            resultMap.put("key1",list1);
            resultMap.put("key2",list2);
            return resultMap;
        } catch (FileNotFoundException e) {
            System.out.println("readfile()   Exception:" + e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public static void testWrit(String savePath, Map<String, List<JSONObject>> resultMap){
        // 写入Excel
        try {
            ExcelEntityDTO excelEntityDTO = new ExcelEntityDTO();
            excelEntityDTO.setFileName("周报-"+new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
            List<ExcelSheetDTO> excelSheetDTOS = new ArrayList<>();
            for (int i =0; i<2; i++){
                if (i == 1){
                    List<String> titleList = ExcelTitleEnum.getTitle();
                    /** 实际的值 */
                    List<JSONObject> objects = resultMap.get("key2");
                    List<Map<String,Object>> valueList = new ArrayList<>();
                    valueList.addAll(objects);
                    ExcelSheetDTO sheetDTO = new ExcelSheetDTO(titleList,valueList);
                    sheetDTO.setSheetName("详情");
                    excelSheetDTOS.add(sheetDTO);
                }else {
                    List<String> titleList = new ArrayList<>();
                    titleList.add("周报");
                    /** 实际的值 */
                    List<JSONObject> objects = resultMap.get("key1");
                    List<Map<String,Object>> valueList = new ArrayList<>();
                    valueList.addAll(objects);
                    ExcelSheetDTO sheetDTO = new ExcelSheetDTO(titleList,valueList);
                    sheetDTO.setSheetName("概述");
                    excelSheetDTOS.add(sheetDTO);
                }

            }
            excelEntityDTO.setExcelSheetDTOS(excelSheetDTOS);
            XSSFWorkbook workbook = writeExcel(excelEntityDTO);
            OutputStream out = new FileOutputStream(savePath+"\\"+excelEntityDTO.getFileName()+".xlsx");
            workbook.write(out);
        } catch (FileNotFoundException e) {
            System.out.println("readfile()   Exception:" + e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
